from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures,csv

class Admin2Fra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='管理员页面', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        DCALLData(self).pack(fill=X,padx=2,pady=2)
        AdDataComFrame(self).pack(fill=X,padx=2,pady=2)
        TaskTableFrame(self).pack(fill=X,padx=2,pady=2)

class AdDataComFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.adCount=IntVar()
        self.radCount=IntVar()
        self.createWidgets()
        Thread(target=self.getkwcount).start()

    def createWidgets(self):


        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='手动补充广告表资料', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        f00=Frame(f0)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        self.dcbtn=Button(f00,text='导出手动补充资料',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.adCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X,side=LEFT,padx=6,pady=2)

        Label(f01, text='已填商家编码文件路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.radCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f01,text='上传手动补充资料',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green',width=20)
        self.drbtn.pack(side=LEFT, padx=2, pady=2)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.radCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def getkwcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ShopId) from ADpro where SJBM is NULL''')
                self.adCount.set(rs[0][0])
                time.sleep(10)
            except Exception as e:
                print(e)
    
    def start_dc(self):

        self.dcbtn.config(state=DISABLED)
        sqlstr='''
                    select
                    ShopID,
                    Purl,
                    SJName as 商家名字,
                    YYName as 运营名字,
                    DpUrl as 店铺首页网址,
                    sjbm as 商家编码
                    from ADPro
                    where Id in (select min(Id) from ADPro where SJBM is null group by ShopID)
                '''
        fname_qz='需手动补充资料'
        dczt=sqlToExcel(fname_qz,self.ms,sqlstr)
        if not dczt:
            print(f'当前无{fname_qz}')
            messagebox.showinfo('提示',f'当前无{fname_qz}')
        else:
            print(f'成功导出到《{dczt}》')
            messagebox.showinfo('提示',f'成功导出到《{dczt}》')

        self.dcbtn.config(state=NORMAL)

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入表格')
            return
        self.drbtn.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:

                cur_shopid=pdata['ShopID']
                cur_sjname=pdata['商家名字']
                cur_yyname=pdata['运营名字']
                cur_dpurl=pdata['店铺首页网址']
                cur_sjbm=pdata['商家编码']


                futures.append(executor.submit(self.up_ad_info,row,[cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_shopid,res_sjname,res_yyname,res_dpurl,res_sjbm,res_aff = future.result()
                    print(f'''第 {res_row} 行,ShopId:{res_shopid},补充资料 =>
                        商家名字:《{res_sjname}》,
                        运营名字:《{res_yyname}》,
                        店铺网址:《{res_dpurl}》,                         
                        商家编码:《{res_sjbm}》,
                        更新行数:{res_aff}
                        ''')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('手动补充资料上传完毕')
        messagebox.showinfo('提示','手动补充资料上传完毕')
        self.drbtn.config(state=NORMAL)

    def up_ad_info(self,row,params):
        cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm=params
        aff=ms.ExecNoQuery('update ADpro set SJName=?,YYName=?,DpUrl=?,sjbm=? where ShopId=?',
                           [cur_sjname,cur_yyname,cur_dpurl,cur_sjbm,cur_shopid])
        return (row,cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm,aff)

class TaskTableFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.createWidgets()

    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f000=Frame(f00)
        f000.pack(fill=X,pady=5)
        Label(f000, text='搜索量小于:').pack(side=LEFT, padx=2, pady=2)
        self.SearchCountIpt = Entry(f000,width=10)
        self.SearchCountIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f000, text='CTR区间:').pack(side=LEFT, padx=2, pady=2)
        self.CTRMinIpt = Entry(f000,width=10)
        self.CTRMinIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f000, text='~').pack(side=LEFT,padx=2, pady=2)
        self.CTRMaxIpt = Entry(f000,width=10)
        self.CTRMaxIpt.pack(side=LEFT, padx=2, pady=2)

        f001=Frame(f00)
        f001.pack(fill=X)
        Label(f001, text='输入小数区间0.00~1.00',fg='red').pack(side=RIGHT)

        

        f002=Frame(f00)
        f002.pack(fill=X)
        self.dcbtn=Button(f002,text='导出点击任务表',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green')
        self.dcbtn.pack(side=LEFT,padx=2, pady=2)

        f01=Frame(f1)
        f01.pack(fill=Y,side=RIGHT)
        PidSearchFrame(f01)

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)

        f2=Frame(self)
        f2.pack(fill=X,side=LEFT)
        ClickUpFrame(f2)

    def start_dc(self):

        if (not self.CTRMinIpt.get().strip()) or (not self.CTRMaxIpt.get().strip()):
            messagebox.showinfo('提示','请输入完整CTR区间')
            return
        self.dcbtn.config(state=DISABLED)
        minctr=float(self.CTRMinIpt.get().strip())
        maxctr=float(self.CTRMaxIpt.get().strip())
        SearchCount=self.SearchCountIpt.get().strip()
        Serstr=f'and Search_Volume<{SearchCount}' if SearchCount else ''
        sqlstr=f'''
            SELECT
                ClickQT,
                Search_Query 关键词,
                Search_Volume 搜索量,
                '' as 产品总页数,
                '' as 按综合位置,
                '' as 按销量位置,
                '' as 月销量,
                '' as 评分,
                '' as 评价数量,
                '' as 总销量,
                SJName 商家名字,
                'https://shopee.co.id/product/' +Shop_ID+'/'+product_id as 产品链接,
                Product_Name_Ad_Name 标题,
                '' as 最低价
            FROM 
                YN_AD_Info
            WHERE
                ClickQT is not null
                and ClickOrNot = 1
                and CTR between ? and ?
                {Serstr}
            ORDER BY 
                product_id,CTR desc
            '''
        print('正在链接数据库...')
        rs= ms.ExecQuerydict(sqlstr,(minctr,maxctr))
        
        datas={}
        row_count=0
        for r in rs:
            cur_purl=r['产品链接']
            cur_kw=r['关键词']
            cur_title=r['标题']
            cur_dj=r['ClickQT']
            isbz=True
            kw_words=[item.lower() for item in cur_kw.split()]
            title_words=[item.lower() for item in cur_title.split()]

            for ckw in kw_words:
                # if ckw.isdigit():
                #     continue
                if ckw not in title_words:
                    isbz=False
                    break
            if isbz:
                r.pop('ClickQT')
                if not datas.get(cur_purl):

                    datas[cur_purl]=[cur_dj,[r]]
                else:
                    datas[cur_purl][1].append(r)
                row_count+=1
        print(f'获取过滤数据:{row_count}行')
        dj_datas=[]

        for cpurl,cr in datas.items():

            cur_dj,cdatas=cr
            if not cur_dj:
                dj_datas.extend(cdatas)
            else:
                if cur_dj<=len(cdatas):
                    task_datas=cdatas[:cur_dj]
                    
                else:
                    repeat_datas=cdatas*(math.ceil(cur_dj/len(cdatas)))
                    task_datas=repeat_datas[:cur_dj]
                
                dj_datas.extend(task_datas)
                print(f'链接:{cpurl},关键词数量:{len(cdatas)},ClickQT:{cur_dj},生成点击行数:{len(task_datas)}')

        print(f'总点击行数: {len(dj_datas)}')

        fname=crate_excel('点击任务表',dj_datas)
        if fname:
            print(f'成功导出点击任务表到 ：《{fname}》')
            messagebox.showinfo('提示',f'成功导出点击任务表到 ：《{fname}》')

        self.dcbtn.config(state=NORMAL)

class ClickUpFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.purlcount=IntVar()
        self.rbmCount=IntVar()
        self.createWidgets()
        Thread(target=self.getcount).start()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X)

        f01=Frame(f0)
        f01.pack(fill=X,padx=2,pady=2)

        self.btn_dc=Button(f01,text='导出修改点击参数',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green')
        self.btn_dc.pack(side=LEFT,padx=2, pady=2)
        Label(f01, textvariable=self.purlcount,fg='red',width=2).pack(side=LEFT, padx=2, pady=2)

        Label(f01, text='路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01,width=10)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.rbmCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        self.btn_dr=Button(f01,text='导入修改点击参数',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green')
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)


    def getcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ProductID) from ADPro where ClickOrNot=1''')
                self.purlcount.set(rs[0][0])
                time.sleep(10)
            except Exception as e:
                print(e)

    def start_dc(self):
        self.btn_dc.config(state=DISABLED)

        sqlstr='''
            select
            ProductNameAdName,
            ProductID,
            Purl,
            SJBM,
            SJName,
            YYName,
            ClickQT,
            ClickOrNot
            from ADPro
            where [id] in (select min(id) from ADPro group by purl) order by SJName desc,ClickOrNot desc
        ''' 
        fname=sqlToExcel('需修改点击参数',self.ms,sqlstr)
        if fname:
            print(f'成功导出:《{fname}》')
            messagebox.showinfo('提示',f'成功导出:《{fname}》')
        
        else:
            print('导出失败')
        self.btn_dc.config(state=NORMAL)
    
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rbmCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入表格')
            return
        self.btn_dr.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:
                cur_pid=pdata['ProductID']
                cur_purl=pdata['Purl']
                cur_ClickQT=pdata['ClickQT']
                if not cur_ClickQT:
                    cur_ClickQT=None
                cur_ClickOrNot=pdata['ClickOrNot']
                futures.append(executor.submit(self.updj,row,[cur_pid,cur_purl,cur_ClickQT,cur_ClickOrNot]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_purl,res_aff,res_aff2 = future.result()
                    print(f'第 {res_row} 行,purl:{res_purl},更新ADPro行数:{res_aff},更新YN_AD_INDO行数:{res_aff2}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('点击参数上传完毕')
        messagebox.showinfo('提示','点击参数上传完毕')
        self.btn_dr.config(state=NORMAL)
    
    def updj(self,row,params):
        pid,purl,ClickQT,ClickOrNot=params
        aff=ms.ExecNoQuery('update AdPro set ClickQT=?,ClickOrNot=? where Purl=?',(ClickQT,ClickOrNot,purl))
        aff2=ms.ExecNoQuery('update YN_AD_Info set ClickQT=?,ClickOrNot=? where Product_ID=?',(ClickQT,ClickOrNot,pid))
        return (row,purl,aff,aff2)
    
class PidSearchFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X,padx=5)
        self.cookiestr=''
        self.datas=[]
        self.statustxt=IntVar()
        self.createWidgets()
        
    def createWidgets(self):

        self.ipF2=Frame(self)
        self.ipF2.pack(pady=2,fill=X,padx=2)

        Label(self.ipF2, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.pathIpt = Entry(self.ipF2)
        self.pathIpt.pack(side=LEFT, padx=2, pady=2)
        Button(self.ipF2,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        Label(self.ipF2, textvariable=self.statustxt,fg='red',width=2).pack(side=LEFT, padx=2, pady=2) 
        btnFra0=Frame(self)
        btnFra0.pack(pady=2,fill=X,padx=2)
        Button(btnFra0,text='生成任务_卡价格',command=lambda:create_dj2(self.fpath)).pack(side=LEFT,fill=X,padx=2)


    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.pathIpt.delete(0, END)
            self.pathIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')


class DCALLData(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.bmCount=IntVar()
        self.rbmCount=IntVar()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X)

        f00=Frame(f0)
        f00.pack(fill=X)

        Label(f00, text='选择数据表:').pack(side=LEFT, padx=4, pady=2)
        self.dbnameSelect = ttk.Combobox(f00,state='readonly',width=40)
        self.dbnameSelect['value'] = [v for v in DBOSDICT.keys()]
        self.dbnameSelect.pack(side=LEFT, padx=5, pady=2)
        self.dbnameSelect.current(0)

        self.dcbtn=Button(f00,text='导出完整数据链',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
    
    def start_dc(self):

        self.dcbtn.config(state=DISABLED)
        alldcbs=['HouTai','HouTai_ALG','YNBigData','ShopeeListCount','ShopeeADCost','ALHome','ALOrder',
                 'ALMessage','ALDiscussion','ShopeeOrder','ShopeeStock']
        cur_dbname=DBOSDICT[self.dbnameSelect.get()]
        orderstr=''
        if cur_dbname=='AllShop':
            orderstr=' order by pcount desc'
        if cur_dbname=='HouTai':
            orderstr='order by groupname'
        if cur_dbname=='ShopHome':
            orderstr='order by groupname'
        if cur_dbname=='ALLGoodPro':
            orderstr='order by visits desc'

        sqlstr=f'select top 100 * from [{cur_dbname}] {orderstr}'

        if cur_dbname in alldcbs:
            sqlstr=f'select * from [{cur_dbname}] {orderstr}'

        fqz=self.dbnameSelect.get()
        cur_map=DBO_MAP.get(cur_dbname)
        fname=sqlToExcel(fqz,self.ms,sqlstr,dbpmap=cur_map)

        if fname:
            print(f'成功导出:《{fname}》')
            messagebox.showinfo('提示',f'成功导出:《{fname}》')
        
        else:
            print('导出失败')
        self.dcbtn.config(state=NORMAL)

import tkinter
list_win=tkinter.Tk()
list_win.wm_geometry("800x650")
list_win.title('ADMIN2')
Admin2Fra(list_win).pack(fill=X,padx=3,pady=3)
list_win.mainloop()